﻿using System;

using Community.CsharpSqlite.SQLiteClient;

using log4net;

using VSTalk.Logger;

namespace VSTalk.Engine.Core.Data.Migration
{
    public class InitialMS : IMigrationScript
    {
        private static readonly ILog _log = LoggerFactory.Create(typeof(InitialMS));
        private SqliteConnection _connection;

        public void Apply(SqliteConnection connection)
        {
            _connection = connection;
            _log.Info("Start updating db");
            _connection.Open();
            try
            {
                CreateVersionTable();
                CreateHistoryTable();
                SetupVersion();
                _log.Info("Db updated successfully");
            }
            catch (Exception e)
            {
                _log.Fatal("Unexpected error", e);
                throw;
            }
            finally
            {
                _connection.Close();
            }
        }

        private void CreateHistoryTable()
        {
            string sql = @"
CREATE TABLE history (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
client_id varchar(32) NOT NULL,
from_jid varchar(3071),
to_jid varchar(3071),
timestamp INTEGER NOT NULL,
message TEXT
)";
            var command = new SqliteCommand(sql, _connection);
            command.ExecuteNonQuery();
        }

        private void CreateVersionTable()
        {
            string sql = "CREATE TABLE version (num INT NOT NULL)";
            var command = new SqliteCommand(sql, _connection);
            command.ExecuteNonQuery();
        }

        public void SetupVersion()
        {
            string sql = "insert into version (num) values (1)";
            var command = new SqliteCommand(sql, _connection);
            command.ExecuteNonQuery();
        }
    }
}